{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "afb470cf-3c66-4033-8f3a-5d0eb80be2a1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://hbu:********@127.0.0.1:2345/hbu\n",
      "环境变量加载完成！\n",
      "数据库连接引擎创建成功！\n"
     ]
    }
   ],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sys\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "# 加载环境变量\n",
    "load_dotenv()\n",
    "\n",
    "# 从环境变量获取数据库连接信息，如果不存在则使用默认值\n",
    "driver=os.getenv('DRIVER', 'postgresql')\n",
    "user=os.getenv('PGUSER', None)\n",
    "password=os.getenv('PGPASSWORD', None)\n",
    "host=os.getenv('PGHOST', None)\n",
    "port=os.getenv('PGPORT', None)\n",
    "database=os.getenv('PGDATABASE', 'postgres')\n",
    "\n",
    "# schema is used for postgres, similiar with database level in MySQL\n",
    "schema=os.getenv('SCHEMA',\"public\")\n",
    "\n",
    "DATABASE_URL = f\"{driver}://{user}:{password}@{host}:{port}/{database}\"\n",
    "\n",
    "if user is not None and password is not None:\n",
    "    print(f\"{driver}://{user}:********@{host}:{port}/{database}\")\n",
    "else:\n",
    "    print('非法的数据库连接URL')\n",
    "    sys.exit(1)\n",
    "print('环境变量加载完成！')\n",
    "\n",
    "# 创建数据库连接引擎\n",
    "try:\n",
    "    engine = create_engine(DATABASE_URL)\n",
    "    print('数据库连接引擎创建成功！')\n",
    "except Exception as e:\n",
    "    print(f'创建数据库连接引擎失败: {e}')\n",
    "    raise\n",
    "# 从PostgreSQL数据库读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4f4c9c96-4a80-45d4-808c-450e520ab7ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>债券简称</th>\n",
       "      <th>最高价出现天数</th>\n",
       "      <th>最高收盘价</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>锦浪转02</td>\n",
       "      <td>0</td>\n",
       "      <td>145.000</td>\n",
       "      <td>2025-11-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>福能转债</td>\n",
       "      <td>4</td>\n",
       "      <td>150.438</td>\n",
       "      <td>2025-11-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>金25转债</td>\n",
       "      <td>3</td>\n",
       "      <td>158.522</td>\n",
       "      <td>2025-10-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>应流转债</td>\n",
       "      <td>4</td>\n",
       "      <td>174.584</td>\n",
       "      <td>2025-10-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>胜蓝转02</td>\n",
       "      <td>1</td>\n",
       "      <td>174.531</td>\n",
       "      <td>2025-09-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>姚记转债</td>\n",
       "      <td>4</td>\n",
       "      <td>148.000</td>\n",
       "      <td>2024-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>锋工转债</td>\n",
       "      <td>2</td>\n",
       "      <td>127.850</td>\n",
       "      <td>2024-02-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>卡倍转02</td>\n",
       "      <td>0</td>\n",
       "      <td>125.000</td>\n",
       "      <td>2024-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>佳禾转债</td>\n",
       "      <td>2</td>\n",
       "      <td>106.200</td>\n",
       "      <td>2024-01-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>东南转债</td>\n",
       "      <td>2</td>\n",
       "      <td>109.036</td>\n",
       "      <td>2024-01-26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>71 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     债券简称  最高价出现天数    最高收盘价        date\n",
       "0   锦浪转02        0  145.000  2025-11-06\n",
       "1    福能转债        4  150.438  2025-11-05\n",
       "2   金25转债        3  158.522  2025-10-30\n",
       "3    应流转债        4  174.584  2025-10-28\n",
       "4   胜蓝转02        1  174.531  2025-09-11\n",
       "..    ...      ...      ...         ...\n",
       "66   姚记转债        4  148.000  2024-03-01\n",
       "67   锋工转债        2  127.850  2024-02-26\n",
       "68  卡倍转02        0  125.000  2024-02-01\n",
       "69   佳禾转债        2  106.200  2024-01-26\n",
       "70   东南转债        2  109.036  2024-01-26\n",
       "\n",
       "[71 rows x 4 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query=\"\"\"\n",
    "WITH ranked_data AS (\n",
    "  SELECT \n",
    "    \"债券简称\",\n",
    "    \"close\",\n",
    "\t\"date\",\n",
    "    ROW_NUMBER() OVER (PARTITION BY \"债券简称\" ORDER BY \"date\" ASC) - 1 as trading_day\n",
    "  FROM \"bond_zh_hs_cov_daily\"\n",
    "  WHERE index < 5\n",
    "),\n",
    "max_close_per_bond AS (\n",
    "  SELECT \n",
    "    \"债券简称\",\n",
    "    MAX(\"close\") as max_close\n",
    "  FROM ranked_data\n",
    "  GROUP BY \"债券简称\"\n",
    ")\n",
    "SELECT \n",
    "  r.\"债券简称\",\n",
    "  r.trading_day as 最高价出现天数,\n",
    "  r.\"close\" as 最高收盘价,\n",
    "  r.\"date\"\n",
    "FROM ranked_data r\n",
    "JOIN max_close_per_bond m ON r.\"债券简称\" = m.\"债券简称\" AND r.\"close\" = m.max_close\n",
    "ORDER BY  r.\"date\" DESC;\n",
    "\"\"\"\n",
    "df = pd.read_sql(query, engine)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "4453f706-f716-41b9-85d3-e8edb6c0d9b4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 71 entries, 0 to 70\n",
      "Data columns (total 4 columns):\n",
      " #   Column   Non-Null Count  Dtype  \n",
      "---  ------   --------------  -----  \n",
      " 0   债券简称     71 non-null     object \n",
      " 1   最高价出现天数  71 non-null     int64  \n",
      " 2   最高收盘价    71 non-null     float64\n",
      " 3   date     71 non-null     object \n",
      "dtypes: float64(1), int64(1), object(2)\n",
      "memory usage: 2.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df['date']=pd.to_datetime(df['date'])\n",
    "# df[(df['date'] >= '2025-01-01')]\n",
    "# ['最高价出现天数'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "fb0246dd-8937-43ed-bac8-e0ba551a07a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "最高价出现天数\n",
       "4    13\n",
       "0     6\n",
       "1     5\n",
       "3     4\n",
       "2     4\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df['date'] >= '2025-01-01')]['最高价出现天数'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d58eec69-5d1f-4a14-b3e6-852a5764bc42",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
